/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controller;

import com.mysql.jdbc.exceptions.jdbc4.MySQLDataException;
import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;
import hibernate.HibernateUtil;
import java.awt.Component;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Scanner;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import view.FrameAdminPassword;

/**
 *
 * @author Felipe
 */
public class MySQL {    
        
    private static MySQL sMySQLConfig = null;
    
    private final String FILE_NAME = "config.properties";     
    private ArrayList<String> mStatements;
    private String mFilePath = "";
    private String mServer = "";
    private String mPort = "";
    private String mUser = "";
    private String mPassword = "";
    private String mDataBase = "";
    private boolean mCanLoad;   
    
    private MySQL() {    
        loadScript();
        try {
            mFilePath = getParent(getClass().getProtectionDomain().getCodeSource().getLocation().toURI().getPath()) + "/";
        } catch (URISyntaxException e) {
            e.printStackTrace();
        }
    }
    
    private void loadScript() {
        mCanLoad = false;
        mStatements = new ArrayList<>();        
        final Scanner scanner = new Scanner(MySQL.class.getResourceAsStream("/script.sql"));
        String statement = "";
        while (scanner.hasNext()) {
            statement = statement + scanner.next() + " ";
            if (statement.contains(";")) {
                mStatements.add(statement);
                statement = "";                
            }
        }
        mCanLoad = true;
    }

    public String getDataBase() {
        return mDataBase;
    }

    public void setDataBase(String pDataBase) {
        mDataBase = pDataBase;
    }

    public String getPassword() {
        return mPassword;
    }

    public void setPassword(String pPassword) {
        mPassword = pPassword;
    }

    public String getPort() {
        return mPort;
    }

    public void setPort(String pPort) {
        mPort = pPort;
    }

    public String getServer() {
        return mServer;
    }

    public void setServer(String pServer) {
        mServer = pServer;
    }

    public String getUser() {
        return mUser;
    }

    public void setUser(String pUser) {
        mUser = pUser;
    }    
    
    public String getUrl() {
        return "jdbc:mysql://" + mServer + ":" + mPort + "/" + mDataBase;
    }
    
    public static MySQL getInstance() {
        return (sMySQLConfig == null) ? sMySQLConfig = new MySQL() : sMySQLConfig;
    }
    
    public void loadFromFile() {
        loadFromFile(true);
        updateHibernateProperties();
    }
    
    private void loadFromFile(boolean pFirst) {    	
        Properties prop = new Properties(); 
    	try {
            prop.load(new FileInputStream(mFilePath + FILE_NAME));
            mServer = prop.getProperty("server");
            mPort = prop.getProperty("port");
            mUser = prop.getProperty("user");
            mPassword = prop.getProperty("password");
            mDataBase = prop.getProperty("database");
    	} catch (IOException e) {
            if (pFirst) {
                createDefaultFile();
                loadFromFile(false);
            }
        }
    }
    
    private void createDefaultFile() {       
        Properties newProp = new Properties();
        try {
            newProp.load(MySQL.class.getResourceAsStream("/config-default.properties"));
            newProp.store(new FileOutputStream(mFilePath + FILE_NAME), null);
        } catch (IOException e) { 
            e.printStackTrace();           
        }
    }
    
    public void saveToFile() {        
        Properties prop = new Properties();         
        try {
            prop.setProperty("server", mServer);
            prop.setProperty("port", mPort);
            prop.setProperty("user", mUser);
            prop.setProperty("password", mPassword);
            prop.setProperty("database", mDataBase);
            prop.store(new FileOutputStream(mFilePath + FILE_NAME), null);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public void updateHibernateProperties() {        
        HibernateUtil.setConnectionProperties(getUrl(), getUser(), getPassword());
    }
    
    private String getParent(String pPath) {
        if (pPath.charAt(pPath.length() - 1) == '/') return pPath;
        for (int i = pPath.length() - 1; i >= 0; i--) {
            if (pPath.charAt(i) == '/' && i != 0) {
                pPath = pPath.substring(0, i);
                return pPath;                
            }           
        }
        return pPath;
    }
    
    public static void handleException(Exception pException) {
        pException = getSQLException(pException);
        MessageController.getInstance().sendExceptionMessage("Houve uma falha na conexão com o banco de dados.", pException);
    }

    public static Exception getSQLException(Exception e) {
        while (true) {
            if (e.getCause() != null) {
                e = (Exception) e.getCause();
            } else {
                break;
            }
            if (e instanceof SQLException || e instanceof MySQLDataException || e instanceof MySQLSyntaxErrorException) {
                return e;
            }
        }
        return e;
    }
    
    public void test() {
        if (!mCanLoad) return;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            DriverManager.getConnection("jdbc:mysql://" + mServer + ":" + mPort + "/" + mDataBase, mUser, mPassword);
            JOptionPane.showMessageDialog(null, "Conexão estabelecida com sucesso.", "Concluído", JOptionPane.INFORMATION_MESSAGE);            
            updateHibernateProperties();
        } catch (Exception e) {
            e.printStackTrace();
            if (String.valueOf(e.getMessage().substring(0, 16)).equals("Unknown database")) {
                Object[] MensagenErro = {"O Banco de Dados '" + mDataBase + "' não existe. \nDeseja criar um novo Banco de Dados?"};
                String[] OpcoesErro = {"Sim", "Não"};

                int ResultadoErro = JOptionPane.showOptionDialog(null, MensagenErro, "Erro", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, OpcoesErro, OpcoesErro[0]);
                if (ResultadoErro == 0) {
                    JTextField jTextFieldNovoBanco = new JTextField();
                    jTextFieldNovoBanco.setText(mDataBase);
                    Object[] MensagenNovoBanco = {"Banco de Dados", jTextFieldNovoBanco};
                    String[] OpcoesNovoBanco = {"OK", "Cancelar"};

                    int ResultadoOpcoes = JOptionPane.showOptionDialog(null, MensagenNovoBanco, "Novo", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, OpcoesNovoBanco, OpcoesNovoBanco[0]);
                    if (ResultadoOpcoes == 0) {
                        try {
                            Class.forName("com.mysql.jdbc.Driver");
                            Connection con;
                            con = DriverManager.getConnection("jdbc:mysql://" + mServer + ":" + mPort, mUser, mPassword);
                            final String banco = jTextFieldNovoBanco.getText();
                            String sql = "CREATE DATABASE " + banco;
                            PreparedStatement pst = con.prepareStatement(sql);
                            pst.executeUpdate();
                            mDataBase = banco;
                            try {
                                Class.forName("com.mysql.jdbc.Driver");
                                con = DriverManager.getConnection(getUrl(), mUser, mPassword);
                                for (int i = 0; i < mStatements.size(); i++) {
                                    con.prepareStatement(mStatements.get(i)).executeUpdate();                            
                                }                       
                                updateHibernateProperties();
                                FrameAdminPassword.getInstance().setVisible(true);
                            } catch (Exception e2) {
                                e2.printStackTrace();
                            }
                        } catch (Exception e3) {
                            JOptionPane.showMessageDialog(null, "Não foi possível criar um novo banco de dados.", "Falha na Conexão", JOptionPane.ERROR_MESSAGE);
                            e3.printStackTrace();
                        }
                    }
                }
            } 
        }     
    }
    
}